﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DVSProject.Models;

namespace DVSProject.Lib
{
    public class CustomerRepo
    {
        public const string m_msg_head = "Customer";

        public IEnumerable<CustomerModels> GetCustomer()
        {
            List<CustomerModels> Customers = new List<CustomerModels>();
            string SQLCommand = @"select * from TBCustomer where Status <>'Delete'";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        CustomerModels Customer = new CustomerModels();
                        Customer.CustId = (string)reader["CustId"];
                        Customer.CustNameTh = (string)reader["CustNameTh"];
                        Customer.CustNameEng = (string)reader["CustNameEng"];
                        Customer.AddressTh = (string)reader["AddressTh"];
                        Customer.AddressEng = (string)reader["AddressEng"];
                        Customer.Tel = (string)reader["Tel"];
                        Customer.CorporateName = (string)reader["CorporateName"];
                        Customer.Status = (string)reader["Status"];
                        Customer.CreateBy = (string)reader["CreateBy"];
                        Customer.CreateOn = (DateTime)reader["CreateOn"];
                        Customer.UpdateBy = (string)reader["UpdateBy"];
                        Customer.UpdateOn = (DateTime)reader["UpdateOn"];

                        Customers.Add(Customer);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return Customers.ToList();
        }

        public CustomerModels GetCustomerById(int id)
        {
            CustomerModels Customer = new CustomerModels();
            string SQLCommand = @"select * from TBCustomer where CustId=@CustId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@CustId", id);
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        Customer.CustId = (string)reader["CustId"];
                        Customer.CustNameTh = (string)reader["CustNameTh"];
                        Customer.CustNameEng = (string)reader["CustNameEng"];
                        Customer.AddressTh = (string)reader["AddressTh"];
                        Customer.AddressEng = (string)reader["AddressEng"];
                        Customer.Tel = (string)reader["Tel"];
                        Customer.CorporateName = (string)reader["CorporateName"];
                        Customer.Status = (string)reader["Status"];
                        Customer.CreateBy = (string)reader["CreateBy"];
                        Customer.CreateOn = (DateTime)reader["CreateOn"];
                        Customer.UpdateBy = (string)reader["UpdateBy"];
                        Customer.UpdateOn = (DateTime)reader["UpdateOn"];
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
           
 
            return Customer;
        }

        public bool Create(CustomerModels Customer)
        {
            bool result = true;
            string SQLCommand = @"INSERT into TBCustomer 
                                    values (@CustNameTh,@CustNameEng,@AddressTh,@AddressEng,
                                     @Tel,@CorporateName,@Status,@UserId,
                                    GETDATE(),@UserId,GETDATE())";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@CustNameTh", Customer.CustNameTh);
                    db.AddParameter("@CustNameEng", Customer.CustNameEng);
                    db.AddParameter("@AddressTh", Customer.AddressTh);
                    db.AddParameter("@AddressEng", Customer.AddressEng);
                    db.AddParameter("@Tel", Customer.Tel);
                    db.AddParameter("@CorporateName", Customer.CorporateName);
                    db.AddParameter("@Status", "Active");
                    db.AddParameter("@UserID", Customer.CreateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Edit(CustomerModels Customer)
        {
            bool result = true;
            string SQLCommand = @"Update TBCustomer 
                                    Set CustNameTh=@CustNameTh,
                                        CustNameEng=@CustNameEng, AddressTh=@AddressTh,
                                        AddressEng=@AddressEng,Tel=@Tel,CorporateName=@CorporateName,
                                        UpdateBy=@UserId,UpdateOn=getdate()
                                    where CustId=@CustId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@CustId", Customer.CustId);
                    db.AddParameter("@CustNameTh", Customer.CustNameTh);
                    db.AddParameter("@CustNameEng", Customer.CustNameEng);
                    db.AddParameter("@AddressTh", Customer.AddressTh);
                    db.AddParameter("@AddressEng", Customer.AddressEng);
                    db.AddParameter("@Tel", Customer.Tel);
                    db.AddParameter("@CorporateName", Customer.CorporateName);
                    db.AddParameter("@UserID", Customer.UpdateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Delete(CustomerModels Customer)
        {
            bool result = true;
            string SQLCommand = @"Update TBCustomer 
                                    Set Status=@Status,UpdateBy=@UserId,UpdateOn=getdate()
                                    where CustId=@CustId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@CustId", Customer.CustId);
                    db.AddParameter("@Status", "Delete");
                    db.AddParameter("@UserID", Customer.UpdateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                    return false;
                }
            }
            return result;
        }
    }
}